﻿using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Text;
using Nop.Core;
using Nop.Data;

namespace Nop.Plugin.Pricing.PreciousMetals.Data
{
    /// <summary>
    /// Object context
    /// </summary>
    public class PreciousMetalsPricingObjectContext : DbContext, IDbContext
    {
        public PreciousMetalsPricingObjectContext(string nameOrConnectionString)
            : base(nameOrConnectionString) { }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Configurations.Add(new PreciousMetalsQuoteMap());
            modelBuilder.Configurations.Add(new PreciousMetalsDetailMap());

            //disable EdmMetadata generation
            modelBuilder.Conventions.Remove<IncludeMetadataConvention>();

            base.OnModelCreating(modelBuilder);
        }

        private string CreateDatabaseScript
        {
            get
            {
                // let entity framework determine the base script
                var sb = new StringBuilder(((IObjectContextAdapter) this).ObjectContext.CreateDatabaseScript());

                // make sure we don't try to create the detail table if it exists already
                sb.Replace("create table [dbo].[PreciousMetalsDetail]",
                           "if object_id(N'[dbo].[PreciousMetalsDetail]') is null create table [dbo].[PreciousMetalsDetail]");

                // version 1.00 -> 1.01 added a field for tier price type
                sb.Append(" if not exists (select * from sys.columns where object_id = object_id('[dbo].[PreciousMetalsDetail]') and name = 'TierPriceType')" +
                          " alter table [dbo].[PreciousMetalsDetail] add [TierPriceType] [int] not null default (0)");

                // version 1.01 -> 1.02 added a field for math type
                sb.Append(" if not exists (select * from sys.columns where object_id = object_id('[dbo].[PreciousMetalsDetail]') and name = 'MathType')" +
                          " alter table [dbo].[PreciousMetalsDetail] add [MathType] [int] not null default (0)");

                // version 1.01 -> 1.02 added a field for lower amount
                sb.Append(" if not exists (select * from sys.columns where object_id = object_id('[dbo].[PreciousMetalsDetail]') and name = 'LowerAmount')" +
                          " alter table [dbo].[PreciousMetalsDetail] add [LowerAmount] decimal(18,2) not null default (0)");

                // version 1.01 -> 1.02 added a field for weight unit
                sb.Append(" if not exists (select * from sys.columns where object_id = object_id('[dbo].[PreciousMetalsDetail]') and name = 'WeightId')" +
                          " alter table [dbo].[PreciousMetalsDetail] add [WeightId] [int] not null default (0)");

                // add an index to the detail table, but only if we didn't create it on the last version
                sb.Append(" if not exists (select * from sys.indexes where name = 'IX_PreciousMetalsDetail_ProductVariantId')" +
                          " create unique nonclustered index [IX_PreciousMetalsDetail_ProductVariantId] on [dbo].[PreciousMetalsDetail] ([ProductVariantId])");

                // add an index to the quote table
                sb.Append(" create nonclustered index [IX_PreciousMetalsQuote_MetalType_DateRetrieved] on [dbo].[PreciousMetalsQuote] ([MetalType], [DateRetrieved])");


                // version 1.03 -> 1.04 add rounding and rounding type
                sb.Append(" if not exists (select * from sys.columns where object_id = object_id('[dbo].[PreciousMetalsDetail]') and name = 'PriceRounding')" +
                          " alter table [dbo].[PreciousMetalsDetail] add [PriceRounding] [int] not null default (0)");

                sb.Append(" if not exists (select * from sys.columns where object_id = object_id('[dbo].[PreciousMetalsDetail]') and name = 'PriceRoundingType')" +
                          " alter table [dbo].[PreciousMetalsDetail] add [PriceRoundingType] [int] not null default (0)");


                //return the script
                return sb.ToString();
            }
        }

        private string DropDatabaseScript
        {
            get
            {
                // Drop the quote table during uninstall.  Leave the detail table there in case we reinstall later.
                return "DROP TABLE PreciousMetalsQuote";
            }
        }

        public new IDbSet<TEntity> Set<TEntity>() where TEntity : BaseEntity
        {
            return base.Set<TEntity>();
        }

        /// <summary>
        /// Install
        /// </summary>
        public void Install()
        {
            Database.ExecuteSqlCommand(CreateDatabaseScript);
            SaveChanges();
        }

        /// <summary>
        /// Uninstall
        /// </summary>
        public void Uninstall()
        {
            Database.ExecuteSqlCommand(DropDatabaseScript);
            SaveChanges();
        }

        public IList<TEntity> ExecuteStoredProcedureList<TEntity>(string commandText, params object[] parameters) where TEntity : BaseEntity, new()
        {
            throw new NotImplementedException();
        }
    }
}
